package nc.impl.hrpbm.deptbuget;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.hrpbm.deptbudget.DeptBudgetRevenueAndExpenditureService;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.jdbc.framework.processor.MapProcessor;
import nc.pub.hrpbm.util.HrpbmReportUtils;
import nc.pub.smart.context.SmartContext;
import nc.pub.smart.data.DataSet;
import nc.pub.smart.metadata.Field;
import nc.pub.smart.metadata.MetaData;
import nc.vo.am.common.util.CollectionUtils;
import nc.vo.pub.BusinessException;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nccloud.commons.lang.ArrayUtils;
import uap.lfw.bq.portal.base.StringUtils;

public class DeptBudgetRevenueAndExpenditureServiceImpl implements DeptBudgetRevenueAndExpenditureService{

    HrpbmReportUtils Utils = new HrpbmReportUtils();
    private BaseDAO baseDAO;
    public BaseDAO getBaseDAO() {
        return baseDAO == null ? new BaseDAO() : baseDAO;
    }

    @Override
    public DataSet sy01process(SmartContext context) throws BusinessException {

        String[] fields = new String[] {"jjname","zctotal_firstdown","zctotal_amount","zctotal_adjust",
                "total_firstdown", "total_amount","total_adjust","inoutbusiclasscode","zcinoutbusiclassname",
                "zcinoutbusiclasscode","xmjjname","xminoutbusiclassname","project_name",
                "xmtotal_firstdown","xmamount_seconddown","xmtotal_adjust","xmtotal_amount","inoutbusiclassname",
                "budgetdept","zcbudgetdept", "xmbudgetdept","budgetdept_code","pk_budgetdept","fundsourcename",
                "xmfundsourcename","pk_fundsource","zctotal_firstup","total_firstup","xmtotal_firstup","xmtotal_secondup"};
        //
        String[] fieldCaptions = new String[] { "支出经济部门", "支出一下复批数", "支出最终预算", "支出最终调整数",
                "一下复批数", "最终预算数","最终调整数", "项目编码", "支出项目名称",
                "支出项目编码","项目部门经济分类","项目收支科目","二级项目"
                ,"项目一下复批","项目二下复批","项目最终调整数", "项目最终预算数","项目名称"
                ,"收入科室","支出科室","收支科室","科室编码","预算部门","资金来源","项目资金来源","资金来源主键"
                ,"支出一上批复数","一上批复数","项目一上批复数","项目二上批复数"};

        //	String[] fieldTypes =new String[] {"String","String","String","String","String","String","String","String","String","String","String","String","String"};
        // TODO Auto-generated method stub
        Utils.handelContext(context);

        //初始化元数据
        //  MetaData meta = getMetaData(fieldTypes, fields);

        //支出02
        //收入
        //项目03
        // 接收数组或列表
        String pkBudgetdept = (String) context.getAttribute("pk_budgetdept");
        List<String> pkBudgetdeptList = new ArrayList<>();
        if(pkBudgetdept != null) {
            pkBudgetdeptList = Arrays.asList(pkBudgetdept.split(","));
        }else {
            pkBudgetdeptList = null;
        }

        String pk_org = (String) context.getAttribute("pk_org");
        String isfund = (String) context.getAttribute("isfund");
        String pk_inoutclass = (String) context.getAttribute("pk_inoutclass");
        String nyear = (String) context.getAttribute("nyear"); // 年度
        String pkFundsource = (String) context.getAttribute("pk_fundsource");//资金来源主键
        if(nyear == null){
            nyear = "2025";
        }
        //String pkbudgetdept = (String) context.getAttribute("pk_budgetdept");

//        String nyear = (String) context.getAttribute("nyear"); // 年度
        //String nyearpk = ""; // 年度主键
        List<Map<String,Object>> businessData1 = new ArrayList<>();
        if(pkFundsource == null){
            businessData1 = executeQuery(getYS01Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList));
        }
        List<Map<String,Object>> businessData2 =executeQuery(getYS02Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList,pkFundsource));
        List<Map<String,Object>> businessData3 =executeQuery(getYS03Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList,pkFundsource));
        if(!businessData1.isEmpty()) {
            businessData1.sort(Comparator.comparing(
                    map -> (String) map.get("inoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        if(!businessData2.isEmpty()) {
            businessData2.sort(Comparator.comparing(
                    map -> (String) map.get("zcinoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        if(!businessData3.isEmpty()) {
            businessData3.sort(Comparator.comparing(
                    map -> (String) map.get("xminoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        List<Map<String,Object>> businessData =  new ArrayList<Map<String,Object>>();

        int maxSize = 0;

        // 比较并找到最大的列表和它的size
        if (businessData1 != null && businessData1.size() > maxSize) {

            maxSize = businessData1.size();
        }
        if (businessData2 != null && businessData2.size() > maxSize) {

            maxSize = businessData2.size();
        }
        if (businessData3 != null && businessData3.size() > maxSize) {

            maxSize = businessData3.size();
        }


        //    List<Map<String,Object>> businessDataAll = businessData1.size()>businessData2.size()?businessData1:businessData2;
        // 初始化合计变量
        BigDecimal zctotal_firstdown = BigDecimal.ZERO;
        BigDecimal zctotal_amount = BigDecimal.ZERO;
        BigDecimal zctotal_adjust = BigDecimal.ZERO;
        BigDecimal total_firstdown = BigDecimal.ZERO;
        BigDecimal total_amount = BigDecimal.ZERO;
        BigDecimal total_adjust = BigDecimal.ZERO;
        BigDecimal xmtotal_firstdown = BigDecimal.ZERO;
        BigDecimal xmtotal_amount = BigDecimal.ZERO;
        BigDecimal xmtotal_adjust = BigDecimal.ZERO;
        BigDecimal xmamount_seconddown = BigDecimal.ZERO;
        BigDecimal inoutbusiclasscode = BigDecimal.ZERO;
        BigDecimal pk_fundsource = BigDecimal.ZERO;
        BigDecimal budgetdept = BigDecimal.ZERO;
        BigDecimal zcbudgetdept = BigDecimal.ZERO;
        BigDecimal xmxmbudgetdept = BigDecimal.ZERO;
        BigDecimal budgetdept_code = BigDecimal.ZERO;
        BigDecimal pk_budgetdept = BigDecimal.ZERO;
        BigDecimal fundsourcename = BigDecimal.ZERO;
        BigDecimal xmfundsourcename = BigDecimal.ZERO;
        BigDecimal zctotal_firstup = BigDecimal.ZERO;
        BigDecimal total_firstup = BigDecimal.ZERO;
        BigDecimal xmtotal_firstup = BigDecimal.ZERO;
        BigDecimal xmtotal_secondup = BigDecimal.ZERO;

        //  int i =0;
        for(int i=0;i<maxSize;i++) {
            Map map = new HashMap<String,Object>();


            map.put("jjname", "");
            map.put("zctotal_firstdown", "");
            map.put("zctotal_amount", "");
            map.put("zctotal_adjust", "");
            map.put("total_firstdown", "");
            map.put("inoutbusiclassname", "");
            map.put("total_amount", "");
            map.put("inoutbusiclasscode", "");
            map.put("classlevel", "");
            map.put("xminoutbusiclassname", "");
            map.put("xminoutbusiclasscode", "");
            map.put("xmtotal_adjust", "");
            map.put("budgetdept","");
            map.put("zcbudgetdept","");
            map.put("xmbudgetdept","");
            map.put("budgetdept_code","");
            map.put("pk_budgetdept","");
            map.put("fundsourcename","");
            map.put("xmfundsourcename","");
            map.put("pk_fundsource","");
            map.put("zctotal_firstup","");
            map.put("total_firstup","");
            map.put("xmtotal_firstup","");
            map.put("xmtotal_secondup","");


            if (i < businessData1.size() ) {
                map.putAll(businessData1.get(i));
                Object FirstDownStr =  businessData1.get(i).get("total_firstdown");
                if (FirstDownStr != null ) {
                    total_firstdown = total_firstdown.add(new BigDecimal(FirstDownStr.toString()));
                }
                Object total_amountStr =  businessData1.get(i).get("total_amount");
                if (total_amountStr != null ) {
                    total_amount = total_amount.add(new BigDecimal(total_amountStr.toString()));
                }
                Object total_adjustStr =  businessData1.get(i).get("total_adjust");
                if (total_adjustStr != null ) {
                    total_adjust = total_adjust.add(new BigDecimal(total_adjustStr.toString()));
                }
                Object FirstUpStr =  businessData1.get(i).get("total_firstup");
                if (FirstUpStr != null ) {
                    total_firstup = total_firstup.add(new BigDecimal(FirstUpStr.toString()));
                }
            }
            if (i < businessData2.size() && !businessData2.get(i).isEmpty()) {
                map.putAll(businessData2.get(i));
                Object zcFirstDownStr =  businessData2.get(i).get("zctotal_firstdown");
                if (zcFirstDownStr != null  ) {
                    zctotal_firstdown = zctotal_firstdown.add(new BigDecimal(zcFirstDownStr.toString()));
                }
                Object zctotal_amountStr =  businessData2.get(i).get("zctotal_amount");
                if (zctotal_amountStr != null  ) {
                    zctotal_amount = zctotal_amount.add(new BigDecimal(zctotal_amountStr.toString()));
                }
                Object zctotal_adjustStr =  businessData2.get(i).get("zctotal_adjust");
                if (zctotal_adjustStr != null ) {
                    zctotal_adjust = zctotal_adjust.add(new BigDecimal(zctotal_adjustStr.toString()));
                }
                Object zcFirstUpStr =  businessData2.get(i).get("zctotal_firstup");
                if (zcFirstUpStr != null  ) {
                    zctotal_firstup = zctotal_firstup.add(new BigDecimal(zcFirstUpStr.toString()));
                }
            }
            if (i < businessData3.size() && !businessData3.get(i).isEmpty()) {
                map.putAll(businessData3.get(i));
                Object xmFirstDownStr =  businessData3.get(i).get("xmtotal_firstdown");
                if (xmFirstDownStr != null  ) {
                    xmtotal_firstdown = xmtotal_firstdown.add(new BigDecimal(xmFirstDownStr.toString()));
                }

                Object xmtotal_amountStr =  businessData3.get(i).get("xmtotal_amount");
                if (xmtotal_amountStr != null  ) {
                    xmtotal_amount = xmtotal_amount.add(new BigDecimal(xmtotal_amountStr.toString()));
                }
                Object xmtotal_adjustStr =  businessData3.get(i).get("xmtotal_adjust");
                if (xmtotal_adjustStr != null ) {
                    xmtotal_adjust = xmtotal_adjust.add(new BigDecimal(xmtotal_adjustStr.toString()));
                }
                Object xmamount_seconddownStr =  businessData3.get(i).get("xmamount_seconddown");
                if (xmamount_seconddownStr != null ) {
                    xmamount_seconddown = xmamount_seconddown.add(new BigDecimal(xmamount_seconddownStr.toString()));
                }
                Object xmFirstUpStr =  businessData3.get(i).get("xmtotal_firstup");
                if (xmFirstUpStr != null  ) {
                    xmtotal_firstup = xmtotal_firstup.add(new BigDecimal(xmFirstUpStr.toString()));
                }
            }

            businessData.add(map);
        }
        Map<String, Object> totalMap = new HashMap<>();
        totalMap.put("zctotal_firstdown", zctotal_firstdown.toString());
        totalMap.put("zctotal_amount", zctotal_amount.toString());
        totalMap.put("zctotal_adjust", zctotal_adjust.toString());
        totalMap.put("zcbudgetdept",zcbudgetdept.toString());
        totalMap.put("total_firstdown", total_firstdown.toString());
        totalMap.put("total_amount", total_amount.toString());
        totalMap.put("total_adjust", total_adjust.toString());
        //totalMap.put("budgetdept",budgetdept.toString());
        totalMap.put("xmtotal_firstdown", xmtotal_firstdown.toString());
        totalMap.put("xmamount_seconddown", xmamount_seconddown.toString());
        totalMap.put("xmtotal_amount", xmtotal_amount.toString());
        totalMap.put("xmtotal_adjust", xmtotal_adjust.toString());
        totalMap.put("xmbudgetdept", xmxmbudgetdept.toBigInteger());
        totalMap.put("budgetdept_code",budgetdept_code.toString());
        totalMap.put("pk_budgetdept",pk_budgetdept.toString());
        totalMap.put("fundsourcename",fundsourcename.toString());
        totalMap.put("xmfundsourcename",xmfundsourcename.toString());
        totalMap.put("pk_fundsource",pk_fundsource.toString());
        totalMap.put("xmtotal_secondup",xmtotal_secondup.toString());
        totalMap.put("xmtotal_firstup",xmtotal_firstup.toString());
        totalMap.put("zctotal_firstup",zctotal_firstup.toString());
        totalMap.put("total_firstup",total_firstup.toString());
        //totalMap.put("total_budgetdept",total_budgetdept.toString());
        totalMap.put("jjname", "本年支出合计");
        totalMap.put("xmjjname", "项目预算合计");
        totalMap.put("inoutbusiclassname", "本年收入合计");

        businessData.add(totalMap);
        MetaData metadata = this.initMetaData(fields, fieldCaptions);

        return buildDataSetobj(metadata, businessData, fields);

    }

    private String buildConditions(String pk_org,String pk_inoutclass,String nyear,
                                   List<String> pkBudgetdeptList, String transtype) {
        StringBuilder builder = new StringBuilder();
        builder.append(" and hrpbm_budgetmake.pk_org = '").append(pk_org).append("' ")
                .append(" and hrpbm_budgetmake.pk_budgetdept <> '1001A110000000LODXTQ' ")// 过滤存在问题的科室
                .append(" and hrpbm_budgetmake.nyear = '").append(nyear).append("' ")
                .append(" and hrpbm_budgetmake.transtype='").append(transtype).append("' ");

        if ("2025".equals(nyear) && "BM20-Cxx-CD-YS02".equals(transtype)) {
            // 过滤掉2025年的设备物资部
            builder.append(" and hrpbm_budgetmake.pk_budgetdept <> '1001A21000000006A8GP' ");
        }

        if (pkBudgetdeptList != null && !pkBudgetdeptList.isEmpty()) {
            builder.append(" and hrpbm_budgetmake.PK_BUDGETDEPT IN ('").append(String.join("','", pkBudgetdeptList)).append("') ");
        }
        if(pk_inoutclass!=null) {
            builder.append(" and detail.pk_inoutclass = '").append(pk_inoutclass).append("' ");
        }
        return builder.toString();
    }

    private String buildDocView() {
        StringBuilder builder = new StringBuilder();
        builder.append(" (select distinct factorvalue5 AS pk_inoutclass,factorvalue2 AS pk_defdoc1,factorvalue3 AS pk_defdoc2 ")
                .append(" from fip_docview_b viewb ")
                .append(" left join fip_docview fview on viewb.pk_classview = fview.pk_classview ")
                .append(" where fview.viewcode = 'YS001') docview ");
        return builder.toString();
    }


    private String getYS01Data(String pk_org,String isfund,String pk_inoutclass,String nyear,List<String> pkBudgetdeptList)
            throws BusinessException {

        //收入  需求字段  项目
        String sql = " SELECT \n" +
                "    bd_inoutbusiclass.pk_parent AS pk_parent,\n" +
                "    bd_inoutbusiclass.code AS inoutbusiclasscode,\n" +
                "    bd_inoutbusiclass.name AS inoutbusiclassname,\n" +
                "    bd_inoutbusiclass.def1 AS classlevel,\n" +
                "    hrpbm_budgetdept.name AS budgetdept,\n" +
                "    hrpbm_budgetdept.code AS budgetdept_code,\n" +
                "    hrpbm_budgetdept.pk_budgetdept AS pk_budgetdept,\n" +
                "    zctotal_firstup as total_firstup,\n" +
                "    zctotal_firstdown as total_firstdown,\n" +
                "    zctotal_adjust as total_adjust,\n" +
                "    zctotal_amount as total_amount\n" +
                "FROM (\n" +
                "    SELECT \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass,\n" +
                "        SUM(zctotal_firstup) AS zctotal_firstup,\n" +
                "        SUM(zctotal_firstdown) AS zctotal_firstdown,\n" +
                "        SUM(zctotal_adjust) AS zctotal_adjust,\n" +
                "        SUM(zctotal_amount) AS zctotal_amount\n" +
                "    FROM (\n" +
                "        SELECT \n" +
                "            NVL(br_inoutclass, bm_inoutclass) AS pk_inoutclass,\n" +
                "            NVL(br_budgetdept, bm_budgetdept) AS pk_budgetdept,\n" +
                "            NVL(br_fundsource, bm_fundsource) AS pk_fundsource,\n" +
                "            zctotal_firstup,\n" +
                "            zctotal_firstdown,\n" +
                "            zctotal_adjust,\n" +
                "            zctotal_amount \n" +
                "        FROM (\n" +
                "            SELECT \n" +
                "                COALESCE(bm.pk_budgetmake, br.pk_budgetmake) AS pk_budgetmake,\n" +
                "                bm.pk_inoutclass AS bm_inoutclass,\n" +
                "                bm.pk_budgetdept AS bm_budgetdept,\n" +
                "                bm.pk_fundsource AS bm_fundsource,\n" +
                "                COALESCE(bm.zctotal_firstup, 0) AS zctotal_firstup,\n" +
                "                COALESCE(bm.zctotal_adjust, 0) AS zctotal_adjust,\n" +
                "                COALESCE(bm.zctotal_amount, 0) AS zctotal_amount,\n" +
                "                br.pk_inoutclass AS br_inoutclass,\n" +
                "                br.pk_budgetdept AS br_budgetdept,\n" +
                "                br.pk_fundsource AS br_fundsource,\n" +
                "                COALESCE(br.zctotal_firstdown, 0) AS zctotal_firstdown\n" +
                "            FROM (\n" +
                "                SELECT\n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    SUM(COALESCE(detail.amount_firstup, 0)) AS zctotal_firstup,\n" +
                "                    SUM(COALESCE(detail.amount_adjust, 0)) AS zctotal_adjust,\n" +
                "                    SUM(COALESCE(detail.amount, 0)) AS zctotal_amount\n" +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetmakedetail detail\n" +
                "                        ON hrpbm_budgetmake.pk_budgetmake = detail.pk_budgetmake\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS01") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource\n" +
                "            ) bm\n" +
                "            FULL OUTER JOIN (\n" +
                "                SELECT \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    SUM(COALESCE(detail.amount_firstdown, 0)) AS zctotal_firstdown\n" +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreply \n" +
                "                        ON hrpbm_budgetreply.pk_firstupbill = hrpbm_budgetmake.pk_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreplydetail detail\n" +
                "                        ON detail.pk_budgetreply = hrpbm_budgetreply.pk_budgetreply\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS01") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource\n" +
                "            ) br\n" +
                "            ON bm.pk_budgetmake = br.pk_budgetmake\n" +
                "                AND bm.pk_inoutclass = br.pk_inoutclass\n" +
                "                AND bm.pk_budgetdept = br.pk_budgetdept\n" +
                "                AND bm.pk_fundsource = br.pk_fundsource\n" +
                "        )\n" +
                "    ) \n" +
                "    GROUP BY \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass\n" +
                ") t\n" +
                " LEFT JOIN bd_inoutbusiclass \n" +
                "    ON bd_inoutbusiclass.pk_inoutbusiclass = t.pk_inoutclass\n" +
                " LEFT JOIN " + buildDocView() +
                "    ON docview.pk_inoutclass = t.pk_inoutclass\n" +
                " LEFT JOIN bd_defdoc doc2 \n" +
                "    ON doc2.pk_defdoc = docview.pk_defdoc2\n" +
                " LEFT JOIN hrpbm_budgetdept \n" +
                "    ON hrpbm_budgetdept.pk_budgetdept = t.pk_budgetdept ";

        if(isfund!=null) {
            sql += " where doc2.code = '"+isfund+"'";
        }

        return sql;
    }
    //支出
    private String getYS02Data(String pk_org,String isfund,String pk_inoutclass,String nyear,List<String> pkBudgetdeptList,String pkFundsource)
            throws BusinessException {

        String sql = " SELECT \n" +
                "    bd_defdoc.name AS jjname,\n" +
                "    bd_inoutbusiclass.pk_parent AS zcparent,\n" +
                "    bd_inoutbusiclass.code AS zcinoutbusiclasscode,\n" +
                "    bd_inoutbusiclass.name AS zcinoutbusiclassname,\n" +
                "    hrpbm_budgetdept.name AS zcbudgetdept,\n" +
                "    hrpbm_budgetdept.code AS budgetdept_code,\n" +
                "    hrpbm_budgetdept.pk_budgetdept AS pk_budgetdept,\n" +
                "    bd_fundsource.name AS fundsourcename,\n" +
                "    bd_fundsource.pk_fundsource AS pk_fundsource,\n" +
                "    zctotal_firstup,\n" +
                "    zctotal_firstdown,\n" +
                "    zctotal_adjust,\n" +
                "    zctotal_amount\n" +
                "FROM (\n" +
                "    SELECT \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass,\n" +
                "        SUM(zctotal_firstup) AS zctotal_firstup,\n" +
                "        SUM(zctotal_firstdown) AS zctotal_firstdown,\n" +
                "        SUM(zctotal_adjust) AS zctotal_adjust,\n" +
                "        SUM(zctotal_amount) AS zctotal_amount\n" +
                "    FROM (\n" +
                "        SELECT \n" +
                "            NVL(br_inoutclass, bm_inoutclass) AS pk_inoutclass,\n" +
                "            NVL(br_budgetdept, bm_budgetdept) AS pk_budgetdept,\n" +
                "            NVL(br_fundsource, bm_fundsource) AS pk_fundsource,\n" +
                "            zctotal_firstup,\n" +
                "            zctotal_firstdown,\n" +
                "            zctotal_adjust,\n" +
                "            zctotal_amount \n" +
                "        FROM (\n" +
                "            SELECT \n" +
                "                COALESCE(bm.pk_budgetmake, br.pk_budgetmake) AS pk_budgetmake,\n" +
                "                bm.pk_inoutclass AS bm_inoutclass,\n" +
                "                bm.pk_budgetdept AS bm_budgetdept,\n" +
                "                bm.pk_fundsource AS bm_fundsource,\n" +
                "                COALESCE(bm.zctotal_firstup, 0) AS zctotal_firstup,\n" +
                "                COALESCE(bm.zctotal_adjust, 0) AS zctotal_adjust,\n" +
                "                COALESCE(bm.zctotal_amount, 0) AS zctotal_amount,\n" +
                "                br.pk_inoutclass AS br_inoutclass,\n" +
                "                br.pk_budgetdept AS br_budgetdept,\n" +
                "                br.pk_fundsource AS br_fundsource,\n" +
                "                COALESCE(br.zctotal_firstdown, 0) AS zctotal_firstdown\n" +
                "            FROM (\n" +
                "                SELECT\n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    SUM(COALESCE(detail.amount_firstup, 0)) AS zctotal_firstup,\n" +
                "                    SUM(COALESCE(detail.amount_adjust, 0)) AS zctotal_adjust,\n" +
                "                    SUM(COALESCE(detail.amount, 0)) AS zctotal_amount\n" +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetmakedetail detail\n" +
                "                        ON hrpbm_budgetmake.pk_budgetmake = detail.pk_budgetmake\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS02") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource\n" +
                "            ) bm\n" +
                "            FULL OUTER JOIN (\n" +
                "                SELECT \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    SUM(COALESCE(detail.amount_firstdown, 0)) AS zctotal_firstdown\n" +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreply \n" +
                "                        ON hrpbm_budgetreply.pk_firstupbill = hrpbm_budgetmake.pk_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreplydetail detail\n" +
                "                        ON detail.pk_budgetreply = hrpbm_budgetreply.pk_budgetreply\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS02") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource\n" +
                "            ) br\n" +
                "            ON bm.pk_budgetmake = br.pk_budgetmake\n" +
                "                AND bm.pk_inoutclass = br.pk_inoutclass\n" +
                "                AND bm.pk_budgetdept = br.pk_budgetdept\n" +
                "                AND bm.pk_fundsource = br.pk_fundsource\n" +
                "        )\n" +
                "    ) \n" +
                "    GROUP BY \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass\n" +
                ") t\n" +
                " LEFT JOIN bd_fundsource \n" +
                "    ON bd_fundsource.pk_fundsource = t.pk_fundsource\n" +
                " LEFT JOIN bd_inoutbusiclass \n" +
                "    ON bd_inoutbusiclass.pk_inoutbusiclass = t.pk_inoutclass\n" +
                " LEFT JOIN " + buildDocView() +
                "    ON docview.pk_inoutclass = t.pk_inoutclass\n" +
                " LEFT JOIN bd_defdoc \n" +
                "    ON bd_defdoc.pk_defdoc = docview.pk_defdoc1\n" +
                " LEFT JOIN bd_defdoc doc2 \n" +
                "    ON doc2.pk_defdoc = docview.pk_defdoc2\n" +
                " LEFT JOIN hrpbm_budgetdept \n" +
                "    ON hrpbm_budgetdept.pk_budgetdept = t.pk_budgetdept ";

        if(isfund!=null) {
            sql += " where doc2.code = '"+isfund+"'";
        }

        return sql;
    }

    //项目
    private String getYS03Data(String pk_org,String isfund,String pk_inoutclass,String nyear,List<String> pkBudgetdeptList,String pkFundsource)
            throws BusinessException {

        //项目  需求字段  项目
        String sql = " SELECT \n" +
                " CASE\n" +
                "            WHEN project.def1 = '2' THEN project.project_name\n" +
                "            WHEN f2project.def1 = '2' THEN f2project.project_name\n" +
                "            WHEN f3project.def1 = '2' THEN f3project.project_name\n" +
                "            ELSE NULL\n" +
                "        END AS project_name," +
                "    bd_defdoc.name AS xmjjname,\n" +
                "    bd_inoutbusiclass.pk_parent AS xmparent,\n" +
                "    bd_inoutbusiclass.code AS xminoutbusiclasscode,\n" +
                "    bd_inoutbusiclass.name AS xminoutbusiclassname,\n" +
                "    hrpbm_budgetdept.name AS xmbudgetdept,\n" +
                "    hrpbm_budgetdept.code AS budgetdept_code,\n" +
                "    hrpbm_budgetdept.pk_budgetdept AS pk_budgetdept,\n" +
                "    bd_fundsource.name AS xmfundsourcename,\n" +
                "    bd_fundsource.pk_fundsource AS pk_fundsource,\n" +
                "    zctotal_firstup as xmtotal_firstup,\n" +
                "    zctotal_firstdown as xmtotal_firstdown,\n" +
                "    amount_seconddown as xmamount_seconddown,\n" +
                "    zctotal_adjust as xmtotal_adjust,\n" +
                "    zctotal_amount as xmtotal_amount " +
                "FROM (\n" +
                "    SELECT \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass,\n" +
                "        pk_project,\n" +
                "        SUM(zctotal_firstup) AS zctotal_firstup,\n" +
                "        SUM(zctotal_firstdown) AS zctotal_firstdown,\n" +
                "        SUM(amount_seconddown) AS amount_seconddown,\n" +
                "        SUM(zctotal_adjust) AS zctotal_adjust,\n" +
                "        SUM(zctotal_amount) AS zctotal_amount\n" +
                "    FROM (\n" +
                "        SELECT \n" +
                "            NVL(br_inoutclass, bm_inoutclass) AS pk_inoutclass,\n" +
                "            NVL(br_budgetdept, bm_budgetdept) AS pk_budgetdept,\n" +
                "            NVL(br_fundsource, bm_fundsource) AS pk_fundsource,\n" +
                "            NVL(br_project, bm_project) AS pk_project,\n" +
                "            zctotal_firstup,\n" +
                "            zctotal_firstdown,\n" +
                "            amount_seconddown,\n" +
                "            zctotal_adjust,\n" +
                "            zctotal_amount \n" +
                "        FROM (\n" +
                "            SELECT \n" +
                "                COALESCE(bm.pk_budgetmake, br.pk_budgetmake) AS pk_budgetmake,\n" +
                "                bm.pk_inoutclass AS bm_inoutclass,\n" +
                "                bm.pk_budgetdept AS bm_budgetdept,\n" +
                "                bm.pk_fundsource AS bm_fundsource,\n" +
                "                bm.pk_project AS bm_project,\n" +
                "                COALESCE(bm.zctotal_firstup, 0) AS zctotal_firstup,\n" +
                "                COALESCE(bm.amount_seconddown, 0) AS amount_seconddown,\n" +
                "                COALESCE(bm.zctotal_adjust, 0) AS zctotal_adjust,\n" +
                "                COALESCE(bm.zctotal_amount, 0) AS zctotal_amount,\n" +
                "                br.pk_inoutclass AS br_inoutclass,\n" +
                "                br.pk_budgetdept AS br_budgetdept,\n" +
                "                br.pk_fundsource AS br_fundsource,\n" +
                "                br.pk_project AS br_project,\n" +
                "                COALESCE(br.zctotal_firstdown, 0) AS zctotal_firstdown\n" +
                "            FROM (\n" +
                "                SELECT\n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    detail.pk_project,\n" +
                "                    SUM(DECODE(hrpbm_budgetmake.taskstage,1,COALESCE(detail.amount_firstup, 0),0)) AS zctotal_firstup, " +
                "                    SUM(DECODE(hrpbm_budgetmake.taskstage,3,COALESCE(detail.amount_seconddown, 0),0)) AS amount_seconddown, " +
                "                    SUM(DECODE(hrpbm_budgetmake.taskstage,3,COALESCE(detail.amount_adjust, 0),0)) AS zctotal_adjust, " +
                "                    SUM(DECODE(hrpbm_budgetmake.taskstage,3,COALESCE(detail.amount, 0),0)) AS zctotal_amount " +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetmakedetail detail\n" +
                "                        ON hrpbm_budgetmake.pk_budgetmake = detail.pk_budgetmake\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS03") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    detail.pk_project\n" +
                "            ) bm\n" +
                "            FULL OUTER JOIN (\n" +
                "                SELECT \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    detail.pk_project,\n" +
                "                    SUM(DECODE(hrpbm_budgetreply.taskstage,2,COALESCE(detail.amount_firstdown, 0),0)) AS zctotal_firstdown " +
                "                FROM \n" +
                "                    hrpbm_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreply \n" +
                "                        ON hrpbm_budgetreply.pk_firstupbill = hrpbm_budgetmake.pk_budgetmake\n" +
                "                    LEFT JOIN hrpbm_budgetreplydetail detail\n" +
                "                        ON detail.pk_budgetreply = hrpbm_budgetreply.pk_budgetreply\n" +
                "                WHERE 1=1 " +
                buildConditions(pk_org,pk_inoutclass,nyear,pkBudgetdeptList,"BM20-Cxx-CD-YS03") +
                "                GROUP BY \n" +
                "                    hrpbm_budgetmake.pk_budgetmake,\n" +
                "                    detail.pk_inoutclass,\n" +
                "                    detail.pk_budgetdept,\n" +
                "                    detail.pk_fundsource,\n" +
                "                    detail.pk_project\n" +
                "            ) br\n" +
                "            ON bm.pk_budgetmake = br.pk_budgetmake\n" +
                "                AND bm.pk_inoutclass = br.pk_inoutclass\n" +
                "                AND bm.pk_budgetdept = br.pk_budgetdept\n" +
                "                AND bm.pk_fundsource = br.pk_fundsource\n" +
                "                AND bm.pk_project = br.pk_project\n" +
                "        )\n" +
                "    ) \n" +
                "    GROUP BY \n" +
                "        pk_fundsource,\n" +
                "        pk_budgetdept,\n" +
                "        pk_inoutclass,\n" +
                "        pk_project\n" +
                ") t\n" +
                " LEFT JOIN bd_fundsource \n" +
                "    ON bd_fundsource.pk_fundsource = t.pk_fundsource\n" +
                " LEFT JOIN bd_inoutbusiclass \n" +
                "    ON bd_inoutbusiclass.pk_inoutbusiclass = t.pk_inoutclass\n" +
                " LEFT JOIN " + buildDocView() +
                "    ON docview.pk_inoutclass = t.pk_inoutclass\n" +
                " LEFT JOIN bd_defdoc \n" +
                "    ON bd_defdoc.pk_defdoc = docview.pk_defdoc1\n" +
                " LEFT JOIN bd_defdoc doc2 \n" +
                "    ON doc2.pk_defdoc = docview.pk_defdoc2\n" +
                " LEFT JOIN hrpbm_budgetdept \n" +
                "    ON hrpbm_budgetdept.pk_budgetdept = t.pk_budgetdept " +
                "    LEFT JOIN bd_project project \n" +
                "        ON t.pk_project = project.pk_project\n" +
                "    LEFT JOIN pm_eps projectclass \n" +
                "        ON project.pk_eps = projectclass.pk_eps\n" +
                "    LEFT JOIN bd_project f2project \n" +
                "        ON f2project.pk_project = project.pk_parentpro\n" +
                "    LEFT JOIN bd_project f3project \n" +
                "        ON f3project.pk_project = f2project.pk_parentpro";

        if(isfund!=null) {
            sql += " where doc2.code = '"+isfund+"'";
        }

        return sql;
    }

    /**
     * 执行sql
     * */
    private List<Map<String,Object>> executeQuery(String sql) throws BusinessException {
        return (ArrayList<Map<String,Object>>)getBaseDAO().executeQuery(sql, new MapListProcessor());
    }

    private List<Map<String,Object>> initList(List<Map<String,Object>> list,List<Map<String,Object>> newlist) throws DAOException {

        //获取上级

        //根据上级分组
        Map<String, List<Map<String, Object>>> grouped = list.stream()
                .collect(Collectors.groupingBy(
                        map -> map.get("pk_parent")==null?"top":(String)map.get("pk_parent"), // 分组键
                        Collectors.toList() // 收集器，这里简单地将元素放入列表中
                ));

        List getList = new ArrayList();


        for (Object key : grouped.keySet()) {
            if(!"top".equals(key)) {

                List<Map<String,Object>> groupedList =grouped.get(key);
                Map<String,Object> newMap = new HashMap<String,Object>();
                //     	 newMap = groupedList.get(0);
                //计算总值

                Double oneup_01Sum = 0.00, oneup_02Sum = 0.00, oneup_03Sum = 0.00;
                String sql = "select code inoutbusiclasscode,pk_parent ,name inoutbusiclassname,pk_inoutbusiclass,def1 classlevel  from bd_inoutbusiclass where pk_inoutbusiclass='"+key.toString()+"'";
                Map<String,Object> fatherMap = (Map<String,Object>)getBaseDAO().executeQuery(sql, new MapProcessor());
                for(Map map: groupedList) {





                    //计算列和

                    oneup_01Sum += ((Number) map.get("amount_firstdown")==null?0:(Number)map.get("amount_firstdown")).doubleValue();


                    oneup_02Sum += ((Number) map.get("amount_adjust")==null?0:(Number)map.get("amount_firstdown")).doubleValue();


                    oneup_03Sum += ((Number) map.get("amount")==null?0:(Number)map.get("amount_firstdown")).doubleValue();




                }
                newMap.put("amount_firstdown", oneup_01Sum);
                newMap.put("amount_adjust", oneup_02Sum);
                newMap.put("amount", oneup_03Sum);

                if(fatherMap!=null) {
                    newMap.put("inoutbusiclassname",fatherMap.get("inoutbusiclassname"));
                    newMap.put("inoutbusiclasscode",fatherMap.get("inoutbusiclasscode"));
                    newMap.put("pk_parent",fatherMap.get("pk_parent"));
                    newMap.put("pk_inoutclass",fatherMap.get("pk_inoutbusiclass"));
                    newMap.put("classlevel",fatherMap.get("classlevel"));
                }
                //计算合计
                // 	newMap.put("amount_oneup",oneup_01Sum+oneup_02Sum+oneup_03Sum+oneup_04Sum+oneup_05Sum);
//	        	newMap.put("amount_onedown",onedown_01Sum+onedown_02Sum+onedown_03Sum+onedown_04Sum+onedown_05Sum);
//	        	newMap.put("amount_twoup",twoup_01Sum+twoup_02Sum+twoup_03Sum+twoup_04Sum+twoup_05Sum);
//	        	newMap.put("amount_twodown",twodown_01Sum+twodown_02Sum+twodown_03Sum+twodown_04Sum+twodown_05Sum);
//

                //  list.add(newMap);

                newlist.add(newMap);
                getList.add(newMap);
            }
        }

        list.addAll(getList);


        if(!getList.isEmpty()) {
            initList(getList,newlist);


        }

        return newlist;



    }
    public static DataSet buildDataSetobj(MetaData meta, List<Map<String,Object>> list, String[] fields) {
        if (ArrayUtils.isEmpty(fields)) {
            ExceptionUtils.wrappBusinessException("报表字段列不能为空，请检查！！！");
        }
        DataSet ds = null;
        if (null == list || list.size() == 0) {
            // 返回空数据集, 用于给语义模型加载空数据用
            Object[][] datas = new Object[1][fields.length];
            ds = new DataSet(meta, datas);
        } else {
            Object[][] datas = new Object[list.size()][fields.length];
            for(int i = 0;i < list.size();i++){
                for(int j = 0; j < fields.length; j++ ) {
                    datas[i][j] = list.get(i).get(fields[j].toString());
                    //vos[i].getAttributeValue(fields[j]);
                }
            }
            ds = new DataSet(meta, datas);
        }
        return ds;
    }
    public static MetaData getMetaData(String[] fieldTypes, String[] fields) {
        if (ArrayUtils.isEmpty(fieldTypes) || ArrayUtils.isEmpty(fields)) {
            ExceptionUtils.wrappBusinessException("报表字段列或字段类型不能为空，请检查！！！");
        }
        // 设置元数据字段的类型和精度
        List<Field> fieldList = new ArrayList<Field>();
        for (int i = 0; i < fieldTypes.length; i++) {
            String javatype = fieldTypes[i];
            Field field = new Field();
            // 用于给报表元数据设置字段名称
            field.setFldname(fields[i]);
            if ("String".equals(javatype)) {
                field.setDbColumnType(Types.VARCHAR);
                // 设置字符串的长度
                field.setPrecision(200);
            } else if ("Double".equals(javatype)) {

//					field.setDbColumnType(Types.VARCHAR);
//					// 设置字符串的长度
//					field.setPrecision(200);
                field.setDbColumnType(Types.DECIMAL);
                field.setPrecision(20);
                field.setScale(2);
            } else if ("Integer".equals(javatype)) {
                field.setDbColumnType(Types.INTEGER);
            } else if ("UFDate".equals(javatype)) {
                field.setDbColumnType(Types.CHAR);
                field.setPrecision(19);
            } else if ("UFBoolean".equals(javatype)) {
                field.setDbColumnType(Types.BOOLEAN);
            }
            fieldList.add(field);
        }
        return new MetaData(fieldList.toArray(new Field[fieldList.size()]));
    }
    private MetaData initMetaData(String[] fields, String[] fieldCaptions) {
        Field[] FieldInfo = initField(fields, fieldCaptions);
        MetaData md = new MetaData();
        for (int i = 0; i < fields.length; i++) {
            md.addField(FieldInfo[i]);
        }
        return md;
    }
    private Field[] initField(String[] fields, String[] fieldCaptions) {
        Field[] FieldInfo = new Field[fields.length];
        for (int i = 0; i < fields.length; i++) {
            Field filed = new Field();
            filed.setCaption(fieldCaptions[i]);
            filed.setDbColumnType(Types.VARCHAR);
            if (i == 2 || i == 3|| i == 4|| i == 5|| i == 6|| i == 1|| i == 16|| i == 15|| i == 13|| i == 14 || i == 26 || i == 27 || i == 28 || i == 29)  { // 5,7对应编制金额，批复金额，金额是否需要调整字段类型，根据情况定
                filed.setDbColumnType(Types.DECIMAL);
                filed.setPrecision(40);
                filed.setScale(2);
            }
            filed.setFldname(fields[i]);
            FieldInfo[i] = filed;
        }
        return FieldInfo;
    }
    public String getYear(String nyearpk) throws BusinessException {

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT periodyear FROM  bd_accperiod  WHERE 1 = 1 \n");
        if (StringUtils.isNotBlank(nyearpk)) {
            sql.append("   AND pk_accperiod = '" + nyearpk + "' \n");// 年度主键
        }

        List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
                new MapListProcessor());
        if (CollectionUtils.isEmpty(sqlResult)) {
            return null;
        }

        return (String) sqlResult.get(0).get("periodyear");
    }



    /**
     * 汇总表
     * */
    @Override
    public DataSet sy02process(SmartContext context) throws BusinessException {

        String[] fields02 = new String[] {"jjname","zctotal_firstdown","zctotal_amount","zctotal_adjust",
                "total_firstdown", "total_amount","total_adjust","inoutbusiclasscode","zcinoutbusiclassname",
                "zcinoutbusiclasscode","xmjjname","xminoutbusiclassname","project_name",
                "xmtotal_firstdown","xmamount_seconddown","xmtotal_adjust","xmtotal_amount","inoutbusiclassname",
                // "budgetdept","zcbudgetdept", "xmbudgetdept","budgetdept_code","pk_budgetdept","fundsourcename",
                "fundsourcename",
                "xmfundsourcename","pk_fundsource","zctotal_firstup","total_firstup","xmtotal_firstup","xmtotal_secondup"};
        //
        String[] fieldCaptions02 = new String[] { "支出经济部门", "支出一下复批数", "支出最终预算", "支出最终调整数",
                "一下复批数", "最终预算数","最终调整数", "项目编码", "支出项目名称",
                "支出项目编码","项目部门经济分类","项目收支科目","二级项目"
                ,"项目一下复批","项目二下复批","项目最终调整数", "项目最终预算数","项目名称"
                //,"收入科室","支出科室","收支科室","科室编码","预算部门","资金来源",
                ,"资金来源",
                "项目资金来源","资金来源主键","支出一上批复数","一上批复数","项目一上批复数","项目二上批复数"};

        Utils.handelContext(context);

        //支出02
        //收入
        //项目03
        // 接收数组或列表
        String pkBudgetdept = (String) context.getAttribute("pk_budgetdept");
        List<String> pkBudgetdeptList = new ArrayList<>();
        if(pkBudgetdept != null) {
            pkBudgetdeptList = Arrays.asList(pkBudgetdept.split(","));
        }else {
            pkBudgetdeptList = null;
        }

        String pk_org = (String) context.getAttribute("pk_org");
        String isfund = (String) context.getAttribute("isfund");
        String pk_inoutclass = (String) context.getAttribute("pk_inoutclass");
        String nyear = (String) context.getAttribute("nyear"); // 年度
        String pkFundsource = (String) context.getAttribute("pk_fundsource");//资金来源主键
        if(nyear == null){
            nyear = "2025";
        }
        //String pkbudgetdept = (String) context.getAttribute("pk_budgetdept");

//        String nyear = (String) context.getAttribute("nyear"); // 年度
        //String nyearpk = ""; // 年度主键
        List<Map<String,Object>> businessData1 = new ArrayList<>();
        String sql = null;
        if(pkFundsource == null){
            sql = getYS01Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList);
            // 分组汇总
            sql = " select sum(total_firstdown) as total_firstdown," +
                    "sum(total_firstup) as total_firstup," +
                    "sum(total_adjust) as total_adjust," +
                    "sum(total_amount) as total_amount," +
                    "pk_parent,inoutbusiclassname,inoutbusiclasscode,classlevel from (" +
                    sql +
                    " ) group by pk_parent,inoutbusiclassname,inoutbusiclasscode,classlevel";
            businessData1 = executeQuery(sql);
        }

        sql = getYS02Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList,pkFundsource);
        // 分组汇总
        sql = " select sum(zctotal_firstdown) as zctotal_firstdown," +
                "sum(zctotal_firstup) as zctotal_firstup," +
                "sum(zctotal_adjust) as zctotal_adjust," +
                "sum(zctotal_amount) as zctotal_amount," +
                "zcinoutbusiclassname,zcinoutbusiclasscode,jjname,fundsourcename,zcparent,pk_fundsource from (" +
                sql +
                " ) group by zcinoutbusiclassname,zcinoutbusiclasscode,jjname,fundsourcename,zcparent,pk_fundsource";
        List<Map<String,Object>> businessData2 = executeQuery(sql);

        sql = getYS03Data(pk_org,isfund,pk_inoutclass,nyear,pkBudgetdeptList,pkFundsource);
        // 分组汇总
        sql = " select sum(xmtotal_firstup) as xmtotal_firstup," +
                "sum(xmtotal_firstdown) as xmtotal_firstdown," +
                "sum(xmtotal_adjust) as xmtotal_adjust," +
                "sum(xmtotal_amount) as xmtotal_amount," +
                "sum(xmamount_seconddown) as xmamount_seconddown," +
                "xminoutbusiclassname,xminoutbusiclasscode,xmjjname,project_name,xmfundsourcename,pk_fundsource from (" +
                sql +
                " ) group by xminoutbusiclassname,xminoutbusiclasscode,xmjjname,project_name,xmfundsourcename,pk_fundsource";
        List<Map<String,Object>> businessData3 = executeQuery(sql);
        if(!businessData1.isEmpty()) {
            businessData1.sort(Comparator.comparing(
                    map -> (String) map.get("inoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        if(!businessData2.isEmpty()) {
            businessData2.sort(Comparator.comparing(
                    map -> (String) map.get("zcinoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        if(!businessData3.isEmpty()) {
            businessData3.sort(Comparator.comparing(
                    map -> (String) map.get("xminoutbusiclasscode"),
                    Comparator.nullsLast(String::compareTo)
            ));
        }
        List<Map<String,Object>> businessData =  new ArrayList<Map<String,Object>>();

        int maxSize = 0;

        // 比较并找到最大的列表和它的size
        if (businessData1 != null && businessData1.size() > maxSize) {

            maxSize = businessData1.size();
        }
        if (businessData2 != null && businessData2.size() > maxSize) {

            maxSize = businessData2.size();
        }
        if (businessData3 != null && businessData3.size() > maxSize) {

            maxSize = businessData3.size();
        }


        //    List<Map<String,Object>> businessDataAll = businessData1.size()>businessData2.size()?businessData1:businessData2;
        // 初始化合计变量
        BigDecimal zctotal_firstdown = BigDecimal.ZERO;
        BigDecimal zctotal_amount = BigDecimal.ZERO;
        BigDecimal zctotal_adjust = BigDecimal.ZERO;
        BigDecimal total_firstdown = BigDecimal.ZERO;
        BigDecimal total_amount = BigDecimal.ZERO;
        BigDecimal total_adjust = BigDecimal.ZERO;
        BigDecimal xmtotal_firstdown = BigDecimal.ZERO;
        BigDecimal xmtotal_amount = BigDecimal.ZERO;
        BigDecimal xmtotal_adjust = BigDecimal.ZERO;
        BigDecimal xmamount_seconddown = BigDecimal.ZERO;
        BigDecimal inoutbusiclasscode = BigDecimal.ZERO;
        BigDecimal pk_fundsource = BigDecimal.ZERO;
        BigDecimal budgetdept = BigDecimal.ZERO;
        BigDecimal zcbudgetdept = BigDecimal.ZERO;
        BigDecimal xmxmbudgetdept = BigDecimal.ZERO;
        BigDecimal budgetdept_code = BigDecimal.ZERO;
        BigDecimal pk_budgetdept = BigDecimal.ZERO;
        BigDecimal fundsourcename = BigDecimal.ZERO;
        BigDecimal xmfundsourcename = BigDecimal.ZERO;
        BigDecimal zctotal_firstup = BigDecimal.ZERO;
        BigDecimal total_firstup = BigDecimal.ZERO;
        BigDecimal xmtotal_firstup = BigDecimal.ZERO;
        BigDecimal xmtotal_secondup = BigDecimal.ZERO;

        //  int i =0;
        for(int i=0;i<maxSize;i++) {
            Map map = new HashMap<String,Object>();


            map.put("jjname", "");
            map.put("zctotal_firstdown", "");
            map.put("zctotal_amount", "");
            map.put("zctotal_adjust", "");
            map.put("total_firstdown", "");
            map.put("inoutbusiclassname", "");
            map.put("total_amount", "");
            map.put("inoutbusiclasscode", "");
            map.put("classlevel", "");
            map.put("xminoutbusiclassname", "");
            map.put("xminoutbusiclasscode", "");
            map.put("xmtotal_adjust", "");
//            map.put("budgetdept","");
//            map.put("zcbudgetdept","");
//            map.put("xmbudgetdept","");
//            map.put("budgetdept_code","");
//            map.put("pk_budgetdept","");
            map.put("fundsourcename","");
            map.put("xmfundsourcename","");
            map.put("pk_fundsource","");
            map.put("zctotal_firstup","");
            map.put("total_firstup","");
            map.put("xmtotal_firstup","");
            map.put("xmtotal_secondup","");


            if (i < businessData1.size() ) {
                map.putAll(businessData1.get(i));
                Object FirstDownStr =  businessData1.get(i).get("total_firstdown");
                if (FirstDownStr != null ) {
                    total_firstdown = total_firstdown.add(new BigDecimal(FirstDownStr.toString()));
                }
                Object total_amountStr =  businessData1.get(i).get("total_amount");
                if (total_amountStr != null ) {
                    total_amount = total_amount.add(new BigDecimal(total_amountStr.toString()));
                }
                Object total_adjustStr =  businessData1.get(i).get("total_adjust");
                if (total_adjustStr != null ) {
                    total_adjust = total_adjust.add(new BigDecimal(total_adjustStr.toString()));
                }
                Object FirstUpStr =  businessData1.get(i).get("total_firstup");
                if (FirstUpStr != null ) {
                    total_firstup = total_firstup.add(new BigDecimal(FirstUpStr.toString()));
                }
            }
            if (i < businessData2.size() && !businessData2.get(i).isEmpty()) {
                map.putAll(businessData2.get(i));
                Object zcFirstDownStr =  businessData2.get(i).get("zctotal_firstdown");
                if (zcFirstDownStr != null  ) {
                    zctotal_firstdown = zctotal_firstdown.add(new BigDecimal(zcFirstDownStr.toString()));
                }
                Object zctotal_amountStr =  businessData2.get(i).get("zctotal_amount");
                if (zctotal_amountStr != null  ) {
                    zctotal_amount = zctotal_amount.add(new BigDecimal(zctotal_amountStr.toString()));
                }
                Object zctotal_adjustStr =  businessData2.get(i).get("zctotal_adjust");
                if (zctotal_adjustStr != null ) {
                    zctotal_adjust = zctotal_adjust.add(new BigDecimal(zctotal_adjustStr.toString()));
                }
                Object zcFirstUpStr =  businessData2.get(i).get("zctotal_firstup");
                if (zcFirstUpStr != null  ) {
                    zctotal_firstup = zctotal_firstup.add(new BigDecimal(zcFirstUpStr.toString()));
                }
            }
            if (i < businessData3.size() && !businessData3.get(i).isEmpty()) {
                map.putAll(businessData3.get(i));
                Object xmFirstDownStr =  businessData3.get(i).get("xmtotal_firstdown");
                if (xmFirstDownStr != null  ) {
                    xmtotal_firstdown = xmtotal_firstdown.add(new BigDecimal(xmFirstDownStr.toString()));
                }

                Object xmtotal_amountStr =  businessData3.get(i).get("xmtotal_amount");
                if (xmtotal_amountStr != null  ) {
                    xmtotal_amount = xmtotal_amount.add(new BigDecimal(xmtotal_amountStr.toString()));
                }
                Object xmtotal_adjustStr =  businessData3.get(i).get("xmtotal_adjust");
                if (xmtotal_adjustStr != null ) {
                    xmtotal_adjust = xmtotal_adjust.add(new BigDecimal(xmtotal_adjustStr.toString()));
                }
                Object xmamount_seconddownStr =  businessData3.get(i).get("xmamount_seconddown");
                if (xmamount_seconddownStr != null ) {
                    xmamount_seconddown = xmamount_seconddown.add(new BigDecimal(xmamount_seconddownStr.toString()));
                }
                Object xmFirstUpStr =  businessData3.get(i).get("xmtotal_firstup");
                if (xmFirstUpStr != null  ) {
                    xmtotal_firstup = xmtotal_firstup.add(new BigDecimal(xmFirstUpStr.toString()));
                }
            }

            businessData.add(map);
        }
        Map<String, Object> totalMap = new HashMap<>();
        totalMap.put("zctotal_firstdown", zctotal_firstdown.toString());
        totalMap.put("zctotal_amount", zctotal_amount.toString());
        totalMap.put("zctotal_adjust", zctotal_adjust.toString());
//        totalMap.put("zcbudgetdept",zcbudgetdept.toString());
        totalMap.put("total_firstdown", total_firstdown.toString());
        totalMap.put("total_amount", total_amount.toString());
        totalMap.put("total_adjust", total_adjust.toString());
        //totalMap.put("budgetdept",budgetdept.toString());
        totalMap.put("xmtotal_firstdown", xmtotal_firstdown.toString());
        totalMap.put("xmamount_seconddown", xmamount_seconddown.toString());
        totalMap.put("xmtotal_amount", xmtotal_amount.toString());
        totalMap.put("xmtotal_adjust", xmtotal_adjust.toString());
//        totalMap.put("xmbudgetdept", xmxmbudgetdept.toBigInteger());
//        totalMap.put("budgetdept_code",budgetdept_code.toString());
//        totalMap.put("pk_budgetdept",pk_budgetdept.toString());
        totalMap.put("fundsourcename",fundsourcename.toString());
        totalMap.put("xmfundsourcename",xmfundsourcename.toString());
        totalMap.put("pk_fundsource",pk_fundsource.toString());
        totalMap.put("xmtotal_secondup",xmtotal_secondup.toString());
        totalMap.put("xmtotal_firstup",xmtotal_firstup.toString());
        totalMap.put("zctotal_firstup",zctotal_firstup.toString());
        totalMap.put("total_firstup",total_firstup.toString());
        //totalMap.put("total_budgetdept",total_budgetdept.toString());
        totalMap.put("jjname", "本年支出合计");
        totalMap.put("xmjjname", "项目预算合计");
        totalMap.put("inoutbusiclassname", "本年收入合计");

        businessData.add(totalMap);

        Field[] FieldInfo = new Field[fields02.length];
        for (int i = 0; i < fields02.length; i++) {
            Field filed = new Field();
            filed.setCaption(fieldCaptions02[i]);
            switch (i) {
                case 1:
                case 2:
                case 3:
                case 4:
                case 5:
                case 6:
                case 13:
                case 14:
                case 15:
                case 16:
                case 21:
                case 22:
                case 23:
                case 24:
                    filed.setDbColumnType(Types.DECIMAL);
                    filed.setPrecision(40);
                    filed.setScale(2);
                    break;
                    default:
                        filed.setDbColumnType(Types.VARCHAR);
                        break;
            }

            filed.setFldname(fields02[i]);
            FieldInfo[i] = filed;
        }

        MetaData md = new MetaData();
        for (int i = 0; i < fields02.length; i++) {
            md.addField(FieldInfo[i]);
        }

        return buildDataSetobj(md, businessData, fields02);

    }


}


